﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.LearningTrain.VehicleTrainingScheduleRecord;

namespace BCData.TMS.LearningTrain
{
    /// <summary>
    /// 培训安排记录
    /// </summary>
    public class VehicleTrainingScheduleRecordData : IVehicleTrainingScheduleRecordData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    VehicleTrainingScheduleRecordId,
		    AgentRecordId,
		    TrainingSiteConfigId,
		    TrainingSiteName,
		    TrainingSiteAddress,
		    AppointmentAvailable,
		    TrainingTitle,
		    TrainingStartDate,
		    TrainingEndDate,
		    TrainingTeacher,
		    CreateTime,
		    EditerId,
		    Editer,
		    TrainingPeriods,
		    IsAllowedExamination,
		    AllowedExaminationTime
        FROM
            VehicleTrainingScheduleRecord";



        #region 构造函数
        public VehicleTrainingScheduleRecordData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync

        /// <summary>
        /// 添加新培训安排记录纪录
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<VehicleTrainingScheduleRecordEntity> InsertAsync(VehicleTrainingScheduleRecordEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion


        #region GetById
        /// <summary>
        /// 获取培训安排记录记录
        /// </summary>
        /// <param name="vehicleTrainingScheduleRecordId"></param>
        /// <returns></returns>
        public async Task<VehicleTrainingScheduleRecordEntity> GetEntityAsync(long vehicleTrainingScheduleRecordId)
        {
            string sql = QUERY + " WHERE  VehicleTrainingScheduleRecordId = ?vehicleTrainingScheduleRecordId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<VehicleTrainingScheduleRecordEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询培训安排记录记录
        public async Task<(IEnumerable<VehicleTrainingScheduleRecordEntity>, int)> GetListAsync(int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();


            string sql = string.Format(@"{0} {1} ; SELECT COUNT(1) FROM  VehicleTrainingScheduleRecord  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize
                  );

            return await this.database.Slave.QueryPageableListAsync<VehicleTrainingScheduleRecordEntity>(sql, parameters);
        }

        #endregion


        public async Task<bool> IsExist(string trainingPeriods, long agentRecordId, long trainingSiteConfigId)
        {
            var sql = @" SELECT COUNT(1) FROM  VehicleTrainingScheduleRecord WHERE AgentRecordId=?AgentRecordId AND 
                                TrainingSiteConfigId=?TrainingSiteConfigId AND TrainingPeriods=?TrainingPeriods; ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<int>(sql, new { trainingPeriods, agentRecordId, trainingSiteConfigId }) > 0;
        }

        public async Task<(IEnumerable<VehicleTrainingScheduleRecordViewEntity>, int)> Query(long? agentRecordId, long? trainingSiteConfigId,
            DateTime? trainDate, int pageIndex, int pageSize)
        {
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>()
            {
                " A.TrainingPeriods IS NOT NULL ",
            };
            if (agentRecordId.HasValue)
            {
                wheres.Add(" A.AgentRecordId=?AgentRecordId ");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (trainingSiteConfigId.HasValue)
            {
                wheres.Add(" A.TrainingSiteConfigId=?TrainingSiteConfigId ");
                parameters.Add("?TrainingSiteConfigId", trainingSiteConfigId.Value, DbType.Int64);
            }
            if (trainDate.HasValue)
            {
                wheres.Add(" A.TrainingStartDate >= ?TrainStartDate ");
                parameters.Add("?TrainStartDate", trainDate.Value.Date, DbType.Date);
                wheres.Add(" A.TrainingStartDate < ?TrainEndDate ");
                parameters.Add("?TrainEndDate", trainDate.Value.AddMonths(1).Date, DbType.Date);
            }
            var sqlQuery = @" SELECT
		                        A.VehicleTrainingScheduleRecordId,
		                        A.AgentRecordId,
		                        A.TrainingSiteConfigId,
		                        A.TrainingSiteName,
		                        A.TrainingSiteAddress,
		                        A.AppointmentAvailable,
		                        A.TrainingTitle,
		                        A.TrainingStartDate,
		                        A.TrainingEndDate,
		                        A.TrainingTeacher,
		                        A.CreateTime,
		                        A.EditerId,
		                        A.Editer,
		                        A.TrainingPeriods,
		                        A.IsAllowedExamination,
		                        A.AllowedExaminationTime,
                                COUNT(B.PendingLearningVehicleId) AS TrainingCount
                            FROM
                                VehicleTrainingScheduleRecord A 
                            LEFT JOIN PendingLearningVehicle B ON A.VehicleTrainingScheduleRecordId=B.VehicleTrainingScheduleRecordId ";
            var sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "";
            var sql = $@"{sqlQuery} {sqlWhere}  GROUP BY A.VehicleTrainingScheduleRecordId  ORDER BY A.VehicleTrainingScheduleRecordId DESC LIMIT {(pageIndex - 1) * pageSize}, {pageSize};
                            SELECT COUNT(1) FROM (SELECT A.VehicleTrainingScheduleRecordId FROM  VehicleTrainingScheduleRecord A 
                        LEFT JOIN PendingLearningVehicle B ON A.VehicleTrainingScheduleRecordId=B.VehicleTrainingScheduleRecordId  
                                    {sqlWhere} GROUP BY A.VehicleTrainingScheduleRecordId )M ";
            return await this.database.Slave.QueryPageableListAsync<VehicleTrainingScheduleRecordViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<VehicleTrainingScheduleRecordEntity>> GetTwicePendingRecord(long agentRecordId, DateTime? Date)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add(" AgentRecordId>=?AgentRecordId ");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int32);
            if (Date.HasValue)
            {
                wheres.Add(" TrainingEndDate>=?StartDate ");
                parameters.Add("?StartDate", Date.Value.Date, DbType.DateTime);
                wheres.Add(" TrainingEndDate<?EndDate ");
                parameters.Add("?EndDate", Date.Value.Date.AddDays(1), DbType.DateTime);
            }
            else
            {
                wheres.Add(" TrainingEndDate>=?StartDate ");
                parameters.Add("?StartDate", DateTime.Now.Date, DbType.DateTime);
            }
            string sql = string.Format(" {0} {1} ORDER BY CreateTime",
               QUERY,
               wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : ""
               );
            return await this.database.Slave.QueryAsync<VehicleTrainingScheduleRecordEntity>(sql, parameters);
        }
    }
}




